﻿using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Reflection;

namespace ImportAndExportExcelFileDemo.Helpers
{
    /// <summary>
    /// 
    /// </summary>
    public static class ExcelHelper
    {
        /// <summary>
        /// 导出excel模板
        /// 参考文档： https://www.cnblogs.com/LaughM/p/16759659.html
        /// </summary>
        /// <returns></returns>
        public static byte[] OutputExcel()
        {
            try
            {
                NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
                NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet");
                IRow Title = sheet.CreateRow(0);
                for (int i = 0; i < 51; i++)
                {
                    Title.CreateCell(i).SetCellValue("title" + i);
                }
                byte[] buffer = new byte[1024 * 5];
                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms, false);
                    //ms.Flush();
                    //ms.Position=0;
                    buffer = ms.ToArray();
                    ms.Close();
                }
                return buffer;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 导出Excel文件
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="entities">数据实体 list</param>
        /// <param name="dicColumns">列对应关系,如Name->姓名</param>
        /// <param name="title">标题</param>
        /// <returns></returns>
        public static byte[] ExportExcel<T>(List<T> entities, Dictionary<string, string> dicColumns, string title = null) where T : class
        {
            if (entities.Count <= 0)
            {
                return null;
            }
            //HSSFWorkbook => xls
            //XSSFWorkbook => xlsx
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("test");//名称自定义
            IRow cellsColumn = null;
            IRow cellsData = null;
            //获取实体属性名
            PropertyInfo[] properties = entities[0].GetType().GetProperties();
            int cellsIndex = 0;
            //标题
            if (!string.IsNullOrEmpty(title))
            {
                ICellStyle style = workbook.CreateCellStyle();
                //边框  
                style.BorderBottom = BorderStyle.Dotted;
                style.BorderLeft = BorderStyle.Hair;
                style.BorderRight = BorderStyle.Hair;
                style.BorderTop = BorderStyle.Dotted;
                //水平对齐  
                style.Alignment = HorizontalAlignment.Left;

                //垂直对齐  
                style.VerticalAlignment = VerticalAlignment.Center;

                //设置字体
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.FontName = "微软雅黑";
                style.SetFont(font);

                IRow cellsTitle = sheet.CreateRow(0);
                cellsTitle.CreateCell(0).SetCellValue(title);
                cellsTitle.RowStyle = style;
                //合并单元格
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, dicColumns.Count - 1));
                cellsIndex = 2;
            }
            //列名
            cellsColumn = sheet.CreateRow(cellsIndex);
            int index = 0;
            Dictionary<string, int> columns = new Dictionary<string, int>();
            foreach (var item in dicColumns)
            {
                cellsColumn.CreateCell(index).SetCellValue(item.Value);
                columns.Add(item.Value, index);
                index++;
            }
            cellsIndex += 1;
            //数据
            foreach (var item in entities)
            {
                cellsData = sheet.CreateRow(cellsIndex);
                for (int i = 0; i < properties.Length; i++)
                {
                    if (!dicColumns.ContainsKey(properties[i].Name)) continue;
                    //这里可以也根据数据类型做不同的赋值，也可以根据不同的格式参考上面的ICellStyle设置不同的样式
                    object[] entityValues = new object[properties.Length];
                    entityValues[i] = properties[i].GetValue(item);
                    //获取对应列下标
                    index = columns[dicColumns[properties[i].Name]];
                    cellsData.CreateCell(index).SetCellValue(entityValues[i]?.ToString());
                }
                cellsIndex++;
            }

            byte[] buffer = null;
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                //don't use buffer = ms.GetBuffer() here, it set many '00'(empty bytes) at the end of
                //the excel stream which will occur error when open the excel file by office(BTW, WPS can open it without error message)
                //reference https://www.cnblogs.com/rangeon/p/10646820.html
                buffer = ms.ToArray();
                ms.Close();
            }

            return buffer;
        }

    }
}
